/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.Product;
import ModelLayer.Supplier;
import java.sql.*;
import java.util.ArrayList;

/**
 *
 * @author Henrik-iMac
 */
public class DBProduct implements IFDBProduct{
    private Connection con;

    public DBProduct(){
        con = DbConnection.getInstance().getDBcon();
    }
    // Get all products
    public ArrayList<Product> getAllProducts(boolean retrieveAssociation){
        ArrayList prodList = new ArrayList();
        prodList = miscWhere("", retrieveAssociation);
        return prodList;
    }
    //get one product
    public Product findProductByPartNo(int partNo, boolean retrieveAssociation){
        String wClause = "partNo = " + partNo;
        System.out.println("Search product: " + wClause);
        return singleWhere(wClause, retrieveAssociation);
    }

    public Product findProductByName(String name, boolean retrieveAssociation){
        String wClause = "name like'%" + name + "%'";
        System.out.println("Search product: "+ wClause);
        return singleWhere(wClause, retrieveAssociation);
    }

    public int insertProduct(Product p){
        int nextPartNo = GetMax.getMaxId("SELECT max(partNo) from Product");
        nextPartNo = nextPartNo + 1;
        System.out.println("Next partNo = " + nextPartNo);

        int rc = -1;
        String query ="INSERT INTO Product(partNo, name, amount, purchasePrice, salesPrice, rentPrice, countryOfOrigin, minStock, supplierId) VALUES("+
                nextPartNo + ", '" +
                p.getName() + "', " +
                p.getAmount() + ", " +
                p.getPurchasePrice() + ", " +
                p.getSalesPrice() + ", " +
                p.getRentPrice() + ", '" +
                p.getCountryOfOrigin() + "', "+
                p.getMinStock() + ", " +
                p.getSupplierId().getZipCode();
        System.out.println("insert: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Insert exception in Product db: "+e);
        }
        return rc;
    }

    public int updateProduct(Product p){
        Product pObj = p;
        int rc = -1;

        String query = "UPDATE Product SET "+
                "partNo = "+ pObj.getPartNo() + ", "+
                "name = '"+ pObj.getName() + "', "+
                "amount = "+ pObj.getAmount() + ", "+
                "purchasePrice = "+ pObj.getPurchasePrice() + ", "+
                "salesPrice = "+ pObj.getSalesPrice() + ", "+
                "rentPrice = "+ pObj.getRentPrice() + ", "+
                "countryOfOrigin = '"+ pObj.getCountryOfOrigin() + "', "+
                "minStock = "+ pObj.getMinStock() +
                " WHERE partNo = "+ pObj.getPartNo();
        System.out.println("Update query: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Update exceptions in product db: "+e);
        }
        return rc;
    }

    public int updateProductAmount(Product p){
        Product pObj = p;
        int rc = -1;

        String query = "UPDATE Product SET "+
                "amount = "+ pObj.getAmount() +
                " WHERE partNo = "+ pObj.getPartNo();
        System.out.println("Update query: "+query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception e) {
            System.out.println("Update exceptions in product db: "+e);
        }
        return rc;
    }

    public int deleteProduct(int partNo){
        int rc = -1;
        String query = "DELETE FROM Product WHERE partNo = "+partNo;
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        } catch (Exception e) {
            System.out.println("Delete exception in product db: "+e);
        }
        return rc;
    }

    private ArrayList<Product> miscWhere(String wClause, boolean retrieveAssociation)
    {
        ResultSet results;
        ArrayList<Product> list = new ArrayList<Product>();

        String query = buildQuery(wClause);

        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            while (results.next()){
                Product pObj = new Product();
                pObj = buildProduct(results);
                list.add(pObj);
            }
            stmt.close();

            if(retrieveAssociation){
                for(Product p : list){
                    IFDBSupplier dbSupObj = new DBSupplier();
                    Supplier sObj = dbSupObj.findSupplierById(p.getSupplierId().getId(), false);
                    System.out.println("Suppler part# is selected");
                    p.setSupplierId(sObj);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception - select: "+e);
            e.printStackTrace();
        }
        return list;
    }

    private Product singleWhere(String wClause, boolean restrieveAssociation){
        ResultSet results;
        Product pObj = new Product();
        pObj.setPartNo(0);

        String query = buildQuery(wClause);
        System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if(results.next()){
                pObj = buildProduct(results);
                stmt.close();
                if(restrieveAssociation){
                    IFDBSupplier dBSuppObj = new DBSupplier();
                    Supplier sObj = dBSuppObj.findSupplierById(pObj.getPartNo(), false);
                    System.out.println("Supplier id is selected");
                    pObj.setSupplierId(sObj);
                }
                else{
                    pObj.setSupplierId(null);
                }
            }
        }
        catch (Exception e) {
            System.out.println("Query exception: "+e);
        }
        return pObj;
    }

    private String buildQuery(String wClause)
    {
        String query="SELECT * FROM Product";
        if (wClause.length()>0){
            query=query+" WHERE "+ wClause;
        }
        return query;
    }

    private Product buildProduct(ResultSet results){
        Product pObj = new Product();
        try {
            pObj.setPartNo(results.getInt(1));
            pObj.setName(results.getString(2));
            pObj.setAmount(results.getInt(3));
            pObj.setPurchasePrice(results.getDouble(4));
            pObj.setSalesPrice(results.getDouble(5));
            pObj.setRentPrice(results.getDouble(6));
            pObj.setCountryOfOrigin(results.getString(7));
            pObj.setMinStock(results.getInt(8));
            pObj.setSupplierId(new Supplier(results.getInt(9)));
        }
        catch (Exception e) {
            System.out.println("error in building the product object");
        }
        return pObj;
    }
}
